package com.jsh.erp.schedule;

import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.jsh.erp.datasource.entities.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.LinkedBlockingQueue;

@Component
@Slf4j
//@RestController
//@RequestMapping("/job")
public class ReportMaterialInventoryDailySchedule {
    @Autowired private SqlSessionFactory sqlSessionFactory;
    /**
     * Cron表达式执行：按照cron表达式定义的时间规则执行
     * 这里表示每分钟的第10秒执行一次
     * @Scheduled(cron = "0 0 1 * * ?")  // 每天1点
     * @Scheduled(cron = "0/10 * * * * ?") //每10秒
     */
    @Scheduled(cron = "0 0 1 * * ?")
//    @GetMapping("/ReportMaterialInventoryDailySchedule")
    public JSONObject execute() {
        JSONArray result = new JSONArray();
        log.info("开始执行每日日报创建任务");
        SqlSession sqlSession = getSqlSession();
        Connection connection = sqlSession.getConnection();

        List<Depot> depots = getDepotList(connection);
        try {
            for (Depot depot : depots){
                List<MaterialVo4Unit> dataList = getListWithStock(connection,depot.getId());
                JSONArray materialArray = new JSONArray();
                for (MaterialVo4Unit vo4Unit : dataList ){
                    JSONObject mJson = new JSONObject();
                    mJson.put("id",vo4Unit.getId());
                    mJson.put("fullBarCode",vo4Unit.getFullBarCode());
                    mJson.put("purchaseDecimal",vo4Unit.getPurchaseDecimal().setScale(2, RoundingMode.HALF_UP).toString());
                    mJson.put("commodityDecimal",vo4Unit.getCommodityDecimal());
                    mJson.put("wholesaleDecimal",vo4Unit.getWholesaleDecimal());
                    mJson.put("lowDecimal",vo4Unit.getLowDecimal());
                    mJson.put("billPrice",vo4Unit.getBillPrice());
                    mJson.put("initialStock",vo4Unit.getInitialStock().intValue());
                    mJson.put("currentStock",vo4Unit.getCurrentStock().intValue());
                    mJson.put("currentStockPrice",vo4Unit.getCurrentStockPrice().setScale(2, RoundingMode.HALF_UP).toString());

                    JSONObject materialDetails = getMaterialDetails(connection,vo4Unit.getId());
                    JSONArray materialDetailsJSONArray = materialDetails.getJSONArray("priceCounts");
                    JSONArray priceArray = new JSONArray();
                    for (int i = 0; i < materialDetailsJSONArray.size(); i++) {
                        if ( materialDetailsJSONArray.getJSONObject(i).getString("depotId").equals(depot.getId().toString()) ){
                            priceArray.add( materialDetailsJSONArray.getJSONObject(i));
                        }
                    }
                    mJson.put("priceCounts",priceArray);

                    materialArray.add(mJson);
                }
                result.add(new JSONObject().fluentPut("depot",depot).fluentPut("mList",materialArray));

                ReportMaterialInventoryDaily reportMaterialInventoryDaily = new ReportMaterialInventoryDaily();
                reportMaterialInventoryDaily.setContent(materialArray.toString());
                reportMaterialInventoryDaily.setDepotId(depot.getId().toString());
                reportMaterialInventoryDaily.setReportDate(DateUtil.date().toDateStr());
                reportMaterialInventoryDaily.setTenantId(63L);
                saveReportMaterialInventoryDaily(connection,reportMaterialInventoryDaily);
            }

        } catch(Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.commit();
            if ( connection != null ){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            sqlSession.close();
        }
        log.info("完成执行每日日报创建任务");
        return new JSONObject().fluentPut("data",result);
    }

    private List<MaterialVo4Unit> getListWithStock(Connection connection,Long depotId){
        Map<Long, BigDecimal> initialStockMap = new HashMap<>();
        List<MaterialInitialStockWithMaterial> initialStockList = getInitialStockWithMaterial(connection,depotId);
        for (MaterialInitialStockWithMaterial mism: initialStockList) {
            initialStockMap.put(mism.getMaterialId(), mism.getNumber());
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        String listWithStockSql =
                "        select m.*, me.commodity_unit unitName, mc.name categoryName, me.bar_code, me.full_bar_code, " +
                "        ifnull(me.purchase_decimal,0) purchase_decimal, " +
                "        ifnull(sum(mcs.current_number),0) currentStock, " +
                "        sum(ifnull(me.purchase_decimal, 0) * ifnull(mcs.current_number, 0)) currentStockPrice, " +
                "        sum(ifnull(m.weight, 0) * ifnull(mcs.current_number, 0)) currentWeight " +
                "        FROM jsh_material m " +
                "        left JOIN jsh_material_extend me on m.id = me.material_id and ifnull(me.delete_Flag,'0') !='1' " +
                "        left join jsh_material_current_stock mcs on m.id = mcs.material_id and ifnull(mcs.delete_flag,'0') !='1' " +
                "        left JOIN jsh_unit u on m.unit_id = u.id and ifnull(u.delete_Flag,'0') !='1' " +
                "        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1' " +
                "        where 1=1 and mcs.depot_id = ? " +
                "        and me.default_flag=1  " +
                "        and ifnull(m.delete_flag,'0') !='1' " +
                "        group by m.id ";

        List<MaterialVo4Unit> dataList = Lists.newArrayList();
        try {
            ps = connection.prepareStatement(listWithStockSql);
            ps.setLong(1, depotId);
            rs = ps.executeQuery();

            while (rs.next()) {
                // 假设rs是查询返回的ResultSet对象
                MaterialVo4Unit materialVo = new MaterialVo4Unit();

                // 设置Material类的所有属性（m.*）
                // 这里包含jsh_material表的所有字段，与之前类似
                materialVo.setId(rs.getLong("id"));
                materialVo.setCategoryId(rs.getLong("category_id"));
                materialVo.setName(rs.getString("name"));
                materialVo.setMfrs(rs.getString("mfrs"));
                materialVo.setModel(rs.getString("model"));
                materialVo.setStandard(rs.getString("standard"));
                materialVo.setColor(rs.getString("color"));
                materialVo.setUnit(rs.getString("unit"));
                materialVo.setRemark(rs.getString("remark"));
                materialVo.setImgName(rs.getString("img_name"));
                materialVo.setUnitId(rs.getLong("unit_id"));
//                materialVo.setExpiryNum(rs.getBigDecimal("expiry_num").intValue());
                materialVo.setWeight(rs.getBigDecimal("weight"));
                materialVo.setEnabled(rs.getBoolean("enabled"));
                materialVo.setOtherField1(rs.getString("other_field1"));
                materialVo.setOtherField2(rs.getString("other_field2"));
                materialVo.setOtherField3(rs.getString("other_field3"));
                materialVo.setEnableSerialNumber(rs.getString("enable_serial_number"));
                materialVo.setEnableBatchNumber(rs.getString("enable_batch_number"));
                materialVo.setPosition(rs.getString("position"));
                materialVo.setTenantId(rs.getLong("tenant_id"));
                materialVo.setDeleteFlag(rs.getString("delete_flag"));
                materialVo.setCaizhi(rs.getString("caizhi"));
                materialVo.setBiaozhun(rs.getString("biaozhun"));
                materialVo.setChangdu(rs.getString("changdu"));
                materialVo.setPaoguangdu(rs.getString("paoguangdu"));
                materialVo.setLianjie(rs.getString("lianjie"));
                materialVo.setMopian(rs.getString("mopian"));
                materialVo.setSupplierId(rs.getLong("supplier_id"));

                // 设置查询中新增的关联字段和计算字段
                materialVo.setUnitName(rs.getString("unitName")); // 来自me.commodity_unit
                materialVo.setCategoryName(rs.getString("categoryName")); // 来自mc.name
                materialVo.setmBarCode(rs.getString("bar_code")); // 来自me.bar_code
                materialVo.setFullBarCode(rs.getString("full_bar_code")); // 来自me.full_bar_code
                materialVo.setPurchaseDecimal(rs.getBigDecimal("purchase_decimal")); // 来自me.purchase_decimal
                materialVo.setCurrentStock(rs.getBigDecimal("currentStock")); // 聚合计算结果
                materialVo.setCurrentStockPrice(rs.getBigDecimal("currentStockPrice")); // 聚合计算结果
                materialVo.setCurrentWeight(rs.getBigDecimal("currentWeight")); // 聚合计算结果
                dataList.add(materialVo);
            }

            for(MaterialVo4Unit item: dataList) {
                item.setUnitName(null!=item.getUnitId()?item.getUnitName() + "[多单位]":item.getUnitName());
                item.setInitialStock(null!=initialStockMap.get(item.getId())?initialStockMap.get(item.getId()):BigDecimal.ZERO);
                item.setBigUnitStock(getBigUnitStock(connection,item.getCurrentStock(), item.getUnitId()));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }
        return dataList;
    }

    private String getBigUnitStock(Connection connection,BigDecimal stock, Long unitId)  {
        String bigUnitStock = "";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * from jsh_unit where id =? and delete_Flag = 0 limit 1";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, unitId);
            rs = ps.executeQuery();
            while (rs.next()) {
                Unit unit = new Unit();
                unit.setRatio(rs.getBigDecimal("ratio"));
                if(unit.getRatio()!=null && unit.getRatio().compareTo(BigDecimal.ZERO)!=0 && stock!=null) {
                    bigUnitStock = stock.divide(unit.getRatio(),2,BigDecimal.ROUND_HALF_UP) + unit.getOtherUnit();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }

        return bigUnitStock;
    }

    private List<MaterialInitialStockWithMaterial> getInitialStockWithMaterial(Connection connection, Long depotId){
        String sql = "select material_id, ifnull(sum(mis.number),0) number " +
                "        from jsh_material_initial_stock mis  " +
                "        where mis.depot_id = ? group by mis.material_id ";

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<MaterialInitialStockWithMaterial> misList = Lists.newArrayList();

        try {
            ps = connection.prepareStatement(sql);
            ps.setLong(1, depotId);
            rs = ps.executeQuery();
            while (rs.next()) {
                MaterialInitialStockWithMaterial mis = new MaterialInitialStockWithMaterial();
                mis.setMaterialId(rs.getLong("material_id"));
                mis.setNumber(rs.getBigDecimal("number"));
                misList.add(mis);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }
        return misList;
    }

    private void saveReportMaterialInventoryDaily(Connection connection, ReportMaterialInventoryDaily reportMaterialInventoryDaily) {
        String sql = "insert into report_material_inventory_daily (report_date, depot_id,content, tenant_id) values (?,?,?,?)";
        PreparedStatement ps = null;

        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, reportMaterialInventoryDaily.getReportDate());
            ps.setString(2, reportMaterialInventoryDaily.getDepotId());
            ps.setString(3, reportMaterialInventoryDaily.getContent());
            ps.setString(4, reportMaterialInventoryDaily.getTenantId().toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,null);
        }
    }

    private List<Depot> getDepotList(Connection connection) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Depot> depotList = Lists.newArrayList();

        try {
            String sql = "SELECT * from jsh_depot where delete_Flag = 0";
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Depot depot = new Depot();
                depot.setId(rs.getLong("id"));
                depot.setName(rs.getString("name"));
                depotList.add(depot);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }

        return depotList;
    }

    /**
     *
     select
     depot_head.create_time,
     depot_item.depot_id,
     depot_head.type,
     depot_head.sub_type,
     depot_item.oper_number,
     depot_item.material_unit,
     depot_item.purchase_decimal,
     depot_item.purchase_unit_price,
     depot_item.tax_last_money,
     depot_item.tax_money,
     depot_item.tax_rate,
     depot_item.tax_unit_price,
     depot_head.number,
     depot_head.link_number,
     depot_item.id,
     depot_item.material_id,
     depot_item.header_id
     from jsh_depot_item as depot_item
     left join jsh_depot_head as depot_head on depot_item.header_id = depot_head.id
     where depot_item.material_id = 4928 and depot_head.status = '1'
     order by depot_head.create_time
     * @param materialId
     */
    public JSONObject getMaterialDetails(Connection connection,Long materialId){
        List<String> logs = Lists.newArrayList();
        MaterialExtend materialExtends = getMaterialExtends(connection,materialId);

        Map<String, Queue<BigDecimal>> depotMaterialPriceQueueMap = Maps.newHashMap();
        List<Map<String, Object>> depotDetailList = selectDepotDetailByMaterialId(connection,materialId);
        for (Map<String,Object> depotDetail : depotDetailList){
            String createTime = depotDetail.get("create_time").toString();
            String depot = depotDetail.get("depot_id") == null ? null : depotDetail.get("depot_id").toString();
            String depotType = depotDetail.get("type").toString();
            String depotSubType = depotDetail.get("sub_type").toString();
            String linkNumber = depotDetail.get("link_number") == null ? null : depotDetail.get("link_number").toString();
            if ( depot == null ){
                logs.add(createTime+", 单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],数量:"+depotDetail.get("oper_number"));
                continue;
            }

            if ( depotMaterialPriceQueueMap.get(depot) == null  ){
                depotMaterialPriceQueueMap.put(depot, new LinkedBlockingQueue<>());
            }

            // 入库逻辑
            if ( depotType.equals("入库") ){
                //采购入库,根据采购入库单中找到采购订单
                if ( depotSubType.equals("采购")  || depotSubType.equals("其它") ){
                    //使用默认单价
                    BigDecimal purchasePrice = materialExtends.getPurchaseDecimal();
//                    logs.add(createTime+", 入库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],数量:"+depotDetail.get("oper_number")+",默认单价:"+purchasePrice);
                    if ( linkNumber != null ){
                        //如果关联订单不为空， 则使用入库单价
                        purchasePrice = getPurchasePrice(linkNumber,depotDetailList);
                        if ( purchasePrice != null ) {
                            logs.add(createTime + ", 入库,单据[" + depotType + "][" + depotSubType + "][" + depotDetail.get("number") + "],数量:" + depotDetail.get("oper_number") + ",关联订单不为空，使用单据[" + linkNumber + "]中的单价:" + purchasePrice);
                        }else{
                            purchasePrice = materialExtends.getPurchaseDecimal();
                        }
                    }

                    if ( purchasePrice == null ){
                        purchasePrice = new BigDecimal(0);
                        logs.add(createTime+", 入库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],数量:"+depotDetail.get("oper_number")+",无默认单价且无关联采购订单，使用默认单价:"+purchasePrice.toString());
                    }

                    int count = new BigDecimal(depotDetail.get("oper_number").toString()).intValue();
                    for (int i=0 ;i<count; i++ ){
                        depotMaterialPriceQueueMap.get(depot).offer(purchasePrice);
                    }
                    logs.add(createTime+", 入库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],入库数量:"+depotDetail.get("oper_number")+",单价:"+purchasePrice.toString()+",库存数量:"+depotMaterialPriceQueueMap.get(depot).size());

                } else if (depotSubType.equals("销售退货")) {
                    //purchase_unit_price有值
                    int count = new BigDecimal(depotDetail.get("oper_number").toString()).intValue();
                    for (int i=0 ;i<count; i++ ){
                        BigDecimal purchasePrice = depotDetail.get("purchase_unit_price") == null ?  materialExtends.getPurchaseDecimal() : new BigDecimal(0);
                        purchasePrice = purchasePrice != null ? purchasePrice : new BigDecimal(0);
                        depotMaterialPriceQueueMap.get(depot).offer(purchasePrice);
                    }
                    logs.add(createTime+", 入库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],数量:"+depotDetail.get("oper_number")+",单价:"+depotDetail.get("purchase_unit_price"));
                }else{

                }
            }

            //出库逻辑
            if ( depotType.equals("出库") ){
                if ( depotSubType.equals("销售") || depotSubType.equals("其它") ){

                    int count = new BigDecimal(depotDetail.get("oper_number").toString()).intValue();
                    Map<String,Integer> purchaseDecimalMaps = Maps.newHashMap();
                    for (int i=0 ;i<count; i++ ){
                        BigDecimal purchaseDecimal = depotMaterialPriceQueueMap.get(depot).poll();
                        if ( purchaseDecimal == null ){
                            purchaseDecimal = materialExtends.getPurchaseDecimal() != null ? materialExtends.getPurchaseDecimal() : new BigDecimal(0);
                            if ( purchaseDecimalMaps.get(purchaseDecimal.toString()) == null ){
                                purchaseDecimalMaps.put(purchaseDecimal.toString(),0);
                            }
                            purchaseDecimalMaps.put(purchaseDecimal.toString(),purchaseDecimalMaps.get(purchaseDecimal.toString())+1);
                            logs.add(createTime+", 出库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],数量:"+depotDetail.get("oper_number")+", 单价:"+purchaseDecimal.toString()+",出库数量不足:"+(i+1)+"/"+count);
                            continue;
                        }
                        if ( purchaseDecimalMaps.get(purchaseDecimal.toString()) == null ){
                            purchaseDecimalMaps.put(purchaseDecimal.toString(),0);
                        }
                        purchaseDecimalMaps.put(purchaseDecimal.toString(),purchaseDecimalMaps.get(purchaseDecimal.toString())+1);
                    }

                    logs.add(createTime+", 出库,单据["+depotType+"]["+depotSubType+"]["+depotDetail.get("number")+"],出库数量:"+depotDetail.get("oper_number")+",库存数量:"+depotMaterialPriceQueueMap.get(depot).size()+",出库价格列表:"+JSONObject.toJSONString(purchaseDecimalMaps));
                }
            }
        }

        JSONArray depotCountPrice = new JSONArray();
        for (String depotId : depotMaterialPriceQueueMap.keySet()){
            Queue<BigDecimal> priceQueue = depotMaterialPriceQueueMap.get(depotId);

            Map<String,Integer> priceCount = Maps.newHashMap();
            priceQueue.forEach(price -> {
                String priceStr = price.toString();
                if ( priceCount.get(priceStr) == null ){
                    priceCount.put(priceStr,0);
                }
                priceCount.put(priceStr, priceCount.get(priceStr)+1);
            });

            depotCountPrice.add(new JSONObject().fluentPut("depotId",depotId).fluentPut("priceCount",priceCount));
        }

        return new JSONObject().fluentPut("priceCounts",depotCountPrice).fluentPut("logs", JSONArray.toJSON(logs));
    }

    private List<Map<String, Object>> selectDepotDetailByMaterialId(Connection connection, Long materialId) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String, Object>> detailList = Lists.newArrayList();

        try {
            String sql = "select " +
                    "        depot_head.create_time, " +
                    "        depot_item.depot_id, " +
                    "        depot_head.type, " +
                    "        depot_head.sub_type, " +
                    "        depot_item.oper_number, " +
                    "        depot_item.material_unit, " +
                    "        depot_item.purchase_decimal, " +
                    "        depot_item.purchase_unit_price, " +
                    "        depot_item.tax_last_money, " +
                    "        depot_item.tax_money, " +
                    "        depot_item.tax_rate, " +
                    "        depot_item.tax_unit_price, " +
                    "        depot_head.number, " +
                    "        depot_head.link_number, " +
                    "        depot_item.id, " +
                    "        depot_item.material_id, " +
                    "        depot_item.header_id " +
                    "        from jsh_depot_item as depot_item " +
                    "        left join jsh_depot_head as depot_head on depot_item.header_id = depot_head.id " +
                    "        where depot_item.material_id = ?  and depot_head.status = '1' " +
                    "        order by depot_head.create_time";
            ps = connection.prepareStatement(sql);
            ps.setLong(1,materialId);
            rs = ps.executeQuery();
            while (rs.next()) {
                Map<String,Object> resultMap = Maps.newHashMap();
                resultMap.put("create_time", rs.getObject("create_time"));
                resultMap.put("depot_id", rs.getObject("depot_id"));
                resultMap.put("type", rs.getObject("type"));
                resultMap.put("sub_type", rs.getObject("sub_type"));
                resultMap.put("oper_number", rs.getObject("oper_number"));
                resultMap.put("material_unit", rs.getObject("material_unit"));
                resultMap.put("purchase_decimal", rs.getObject("purchase_decimal"));
                resultMap.put("purchase_unit_price", rs.getObject("purchase_unit_price"));
                resultMap.put("tax_last_money", rs.getObject("tax_last_money"));
                resultMap.put("tax_money", rs.getObject("tax_money"));
                resultMap.put("tax_rate", rs.getObject("tax_rate"));
                resultMap.put("tax_unit_price", rs.getObject("tax_unit_price"));
                resultMap.put("number", rs.getObject("number"));
                resultMap.put("link_number", rs.getObject("link_number"));
                resultMap.put("id", rs.getObject("id"));
                resultMap.put("material_id", rs.getObject("material_id"));
                resultMap.put("header_id", rs.getObject("header_id"));
                detailList.add(resultMap);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }

        return detailList;
    }

    private MaterialExtend getMaterialExtends(Connection connection, Long materialId) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        MaterialExtend materialExtend = null ;

        try {
            String sql = "select * from jsh_material_extend where material_id = ? and delete_flag = 0 ";
            ps = connection.prepareStatement(sql);
            ps.setLong(1, materialId);
            rs = ps.executeQuery();
            while (rs.next()) {
                materialExtend = new MaterialExtend();
                materialExtend.setId(rs.getLong("id"));
                materialExtend.setMaterialId(rs.getLong("material_id"));
                materialExtend.setPurchaseDecimal(rs.getBigDecimal("purchase_decimal"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }

        return materialExtend;
    }

    private Map<String,Object> getMaterialFullInfo(Connection connection, Long materialId){
        String sql = "select m.*,u.name unit_name,mc.name categoryName,mc.id categoryId,me.bar_code m_bar_code,me.id meId,me.commodity_unit,me.sku,me.low_decimal,me.purchase_decimal,me.id as extends_id,me.full_bar_code from jsh_material m  " +
                "        left join jsh_material_extend me on m.id=me.material_id and ifnull(me.delete_Flag,'0') !='1'  " +
                "        left join jsh_unit u on m.unit_id=u.id and ifnull(u.delete_Flag,'0') !='1'  " +
                "        left JOIN jsh_material_category mc on m.category_id = mc.id and ifnull(mc.delete_Flag,'0') !='1'  " +
                "        where m.id = ? and m.enabled=1 and me.id is not null  " +
                "                 and ifnull(m.delete_flag,'0') !='1'  " +
                "        ORDER BY m.id desc, me.default_flag desc, me.id asc limit 1";

        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String, Object> resultMap = Maps.newHashMap();
        try {
            ps = connection.prepareStatement(sql);
            ps.setLong(1, materialId);
            rs = ps.executeQuery();
            if (rs.next()) {


                // 1. 填充jsh_material表的字段（m.*）
                // 注意：m.*包含该表所有字段，以下列出可确定的字段，需根据实际表结构补充其他字段
                resultMap.put("id", rs.getObject("id")); // m.id
//                resultMap.put("enabled", rs.getObject("enabled")); // m.enabled
//                resultMap.put("delete_flag", rs.getObject("delete_flag")); // m.delete_flag
                // 补充m表其他字段（如name、code等，根据实际表结构添加）
                // resultMap.put("name", rs.getObject("name"));
                // resultMap.put("code", rs.getObject("code"));

                // 2. 填充jsh_unit表的字段
                resultMap.put("unit_name", rs.getObject("unit_name")); // u.name unit_name

                // 3. 填充jsh_material_category表的字段
                resultMap.put("categoryName", rs.getObject("categoryName")); // mc.name categoryName
                resultMap.put("categoryId", rs.getObject("categoryId")); // mc.id categoryId

                // 4. 填充jsh_material_extend表的字段（含别名）
                resultMap.put("m_bar_code", rs.getObject("m_bar_code")); // me.bar_code m_bar_code
                resultMap.put("meId", rs.getObject("meId")); // me.id meId
                resultMap.put("commodity_unit", rs.getObject("commodity_unit")); // me.commodity_unit
                resultMap.put("sku", rs.getObject("sku")); // me.sku
                resultMap.put("low_decimal", rs.getObject("low_decimal")); // me.low_decimal
                resultMap.put("purchase_decimal", rs.getObject("purchase_decimal")); // me.purchase_decimal
                resultMap.put("extends_id", rs.getObject("extends_id")); // me.id as extends_id
                resultMap.put("full_bar_code", rs.getObject("full_bar_code")); // me.full_bar_code

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(ps,rs);
        }
        return resultMap;
    }

    //根据采购入库单中link_number找到本次入库的材料单价
    public BigDecimal getPurchasePrice(String cgddNumber, List<Map<String, Object>> depotDetailList){
        for (Map<String,Object> depotDetail : depotDetailList){
            if ( cgddNumber.equals(depotDetail.get("number").toString()) ){
                return depotDetail.get("purchase_decimal") != null ? new BigDecimal(depotDetail.get("purchase_decimal").toString()) : new BigDecimal(0);
            }
        }
        return null ;
    }

    public SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        try {
            // 1. 获取SqlSession（MyBatis的会话对象）
            sqlSession = sqlSessionFactory.openSession();
            // 2. 从SqlSession中获取底层JDBC连接
        }catch (Exception e){
            e.printStackTrace();
        }
        return sqlSession;
    }

    public void close(PreparedStatement ps, ResultSet rs){
        if ( rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if ( ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
